<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/10/20 0020
 * Time: 09:50
 */

namespace app\currency\service;


use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class PhpSpreadsheetService
{
    /**
     * 数据导出excel表
     * @param string $name excel表的表名
     * @param array $head excel表的表头，接受一个数组，最多二维数组
     * @param array $data 要导出excel表的数据，接受一个二维数组
     * @param int $width 表格宽度
     * 备注：此函数缺点是，表头（对应列数）不能超过26；
     * 循环不够灵活，一个单元格中不方便存放两个数据库字段的值
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public static function export($name='unname', $head=[], $data=[], $width = 20)
    {

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getStyle('A:'.strtoupper(chr($width + 65)))->applyFromArray(['alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER,
            'wrapText' => true
        ]]);

        $i = 65;
        foreach ($head as $item) {
            if(isset($item['child']) && !empty($item['child'])){
                $count = count($item['child']);
                $j = $i;
                foreach ($item['child'] as $value) {
                    // 插入子标题
                    $sheet->getColumnDimension(chr($j))->setWidth($value['width']);
                    $sheet->setCellValue(strtoupper(chr($j)) . '2', $value['title']);
                    $j++;
                }
                // 横向合并
                $sheet->mergeCells(chr($i).'1:'.chr($i+$count-1).'1');
                $sheet->setCellValue(strtoupper(chr($i)) . '1', $item['title']);
                $i += $count; // 计数器加上横向合并总数
            } else {
                $sheet->getColumnDimension(chr($i))->setWidth($item['width']);
                // 竖向合并
                $sheet->mergeCells(chr($i).'1:'.chr($i).'2');
                $sheet->setCellValue(strtoupper(chr($i)) . '1', $item['title']);
                $i++; // 计数器加1
            }
        }
        // 设置抬头字体加粗
        $sheet->getStyle('A1:'.strtoupper(chr($i-1)).'2')
            ->getFont()
            ->setBold(true);
        // 设置表格抬头边框
        $borderArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => '000'],
                ],
            ],
        ];
        $sheet->getStyle('A1:'.strtoupper(chr($i-1)).'2')
            ->applyFromArray($borderArray);
        $current = 3;
        $i = 65;
        foreach ($data as $datum) {
            $max_num = 1; // 纵向排列最大值
            $chargeColumn = [];
            foreach ($head as $item) {
                // 单列数据
                $v = $datum[$item['field']];
                if(isset($item['child']) && !empty($item['child'])){
                    $count = count($item['child']);
                    $data_count = count($v);
                    $max_num = $data_count > $max_num ? $data_count : $max_num;
                    foreach ($v as $k => $child_data) {
                        $j = $i;
                        foreach ($item['child'] as $value) {
                            $sheet->setCellValueExplicit(strtoupper(chr($j)) . ($current+$k), $child_data[$value['field']], DataType::TYPE_STRING);
                            $j++;
                        }
                    }
                    $i += $count;
                } else {
                    $chargeColumn[] = $i;
                    $sheet->setCellValueExplicit(strtoupper(chr($i)) . ($current),$v,DataType::TYPE_STRING);
                    $i++;
                }
            }
            $i = 65; // 复位横向位置
            // 纵向合并单元格
            foreach ($chargeColumn as $charge) {
                $sheet->mergeCells(chr($charge).$current.':'.chr($charge).($current+$max_num-1));
            }
            $current += $max_num; // 调整纵向位置
        }
        //设置行高
        for ($i=3;$i<$current;$i++){
            $sheet->getRowDimension($i)->setRowHeight('20');
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');

        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');

        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }
}